module Octopus.OctopusService.Database.Core

open System.Data
open System.Data.SqlClient
open System.Data.SqlTypes
open System.Threading
open Octopus.OctopusLib
open Octopus.OctopusService.General


let internal connectoToOctopusDb () =
  let conn = new System.Data.SqlClient.SqlConnection()
  conn.ConnectionString <- @"Data Source=.\SQLExpress;Initial Catalog=OctopusDb;Integrated Security=SSPI"
  conn.Open()
  conn
  

//-----------------------------------------

let checkDatabaseExists () =
    use conn = new System.Data.SqlClient.SqlConnection()
    conn.ConnectionString <- @"Data Source=.\SQLExpress;Initial Catalog=master;Integrated Security=SSPI"
    conn.Open()

    let query = "SELECT name FROM sys.databases WHERE name = N'OctopusDb'"
    try
      use cmd = new SqlCommand(query, conn)
      use reader = cmd.ExecuteReader()
      reader.Read()
    finally
      conn.Close()    


let createDatabaseFile () =
    let exNonQ conn query =
      use cmd = new SqlCommand(query, conn, CommandTimeout=10)
      cmd.ExecuteNonQuery()

    use conn = new System.Data.SqlClient.SqlConnection()
    conn.ConnectionString <- @"Data Source=.\SQLExpress;Initial Catalog=master;Integrated Security=SSPI"
    conn.Open()

    try
        let databasePath = (buildFilePath getServiceBinPath "Octopus.mdf").FullName
        let databaseLogPath = (buildFilePath getServiceBinPath "Octopus_log.LDF").FullName
    
        exNonQ conn "USE [master]" |> ignore

        // Create the database
        let sqlCmdText =
          "CREATE DATABASE [OctopusDb] ON PRIMARY " +
          (sprintf "(NAME = N'Octopus', FILENAME = N'%s' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB) " databasePath) +
          " LOG ON " +
          (sprintf "(NAME = N'Octopus_log', FILENAME = N'%s' , SIZE = 8192KB , MAXSIZE = 25600KB , FILEGROWTH = 10%%) " databaseLogPath) +
          " COLLATE Latin1_General_CI_AS"

        exNonQ conn sqlCmdText |> ignore

        exNonQ conn "USE [OctopusDb]" |> ignore
        exNonQ conn "SET ANSI_NULLS ON" |> ignore
        exNonQ conn "SET QUOTED_IDENTIFIER ON" |> ignore

        (*
        // Create the ClipsCommandsHistory table
        let sqlCmdText =
          "CREATE TABLE [dbo].[ClipsCommandsHistory](" +
          "	[ID] [int] IDENTITY(1,1) NOT NULL," +
          "	[UserOriginatedFrom] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL," +
          "	[UserTargetedTo] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL," +
          "	[ClipsCommand] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL," +
          "	[ClipsCommandArgs] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL," +
          "	[InsertedOn] [datetime] NOT NULL," +
          "	[IsExecuted] [bit] NOT NULL," +
          "	[ExecutedOn] [datetime] NOT NULL," +
          " CONSTRAINT [PK_ClipsCommandsHistory] PRIMARY KEY CLUSTERED" +
          " ( [ID] ASC ) WITH ( PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF ) ON [PRIMARY]" +
          " ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"

        exNonQ conn sqlCmdText |> ignore
        *)

        // Create the VirtualMachinesRequested table
        let sqlCmdText =
          "CREATE TABLE [dbo].[VirtualMachinesRequested](" +
          "	[ID] [int] IDENTITY(1,1) NOT NULL," +
          "	[OctopusGuid] [uniqueidentifier] NOT NULL," +
          " [NumberOfVm] [int] NOT NULL," +
          "	[OwnerName] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL," +
          "	[MachineNames] [nvarchar](64) COLLATE Latin1_General_CI_AS NOT NULL," +
          "	[Password] [nvarchar](128) COLLATE Latin1_General_CI_AS NOT NULL," +
          "	[Description] [ntext] COLLATE Latin1_General_CI_AS NULL," +
          "	[CpuCount] [int] NOT NULL," +
          "	[MemorySize] [bigint] NOT NULL," +
          "	[VmTemplateID] [int] NOT NULL," +
          "	[Allowed] [bit] NULL," +
          " CONSTRAINT [PK_VirtualMachinesRequested] PRIMARY KEY CLUSTERED" +
          " ( [ID] ASC ) WITH ( PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF ) ON [PRIMARY]" +
          " ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"

        exNonQ conn sqlCmdText |> ignore

        // Create the VirtualMachinesData table
        let sqlCmdText =
          "CREATE TABLE [dbo].[VirtualMachinesData](" +
          "	[ID] [int] IDENTITY(1,1) NOT NULL," +
          "	[OctopusGuid] [uniqueidentifier] NOT NULL," +
          //" [SecurityToken] [uniqueidentifier] NOT NULL," +
          "	[OwnerName] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL," +
          "	[MachineName] [nvarchar](64) COLLATE Latin1_General_CI_AS NOT NULL," +
          //" [Name] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL," +
          "	[Description] [ntext] COLLATE Latin1_General_CI_AS NULL," +
          "	[CpuCount] [int] NOT NULL," +
          "	[MemorySize] [bigint] NOT NULL," +
          "	[OctopusState] [int] NOT NULL," +
          "	[MacAddress] [nchar](12) COLLATE Latin1_General_CI_AS NOT NULL," +
          "	[VmTemplateID] [int] NOT NULL," +
          " CONSTRAINT [PK_VirtualMachinesData] PRIMARY KEY CLUSTERED" +
          " ( [ID] ASC ) WITH ( PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF ) ON [PRIMARY]" +
          " ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"

        exNonQ conn sqlCmdText |> ignore

        // Create the VirtualMachinesActive table
        let sqlCmdText =
          "CREATE TABLE [dbo].[VirtualMachinesActive](" +
          "	[ID] [int] IDENTITY(1,1) NOT NULL," +
          "	[VmDataID] [int] NOT NULL," +
          "	[OctopusGuid] [uniqueidentifier] NOT NULL," +
          "	[HyperVGuid] [uniqueidentifier] NOT NULL," +
          "	[CurrentServer] [nvarchar](64) COLLATE Latin1_General_CI_AS NOT NULL," +
          "	[StoredPath] [nvarchar](256) COLLATE Latin1_General_CI_AS NOT NULL," +
          "	[HyperVState] [int] NOT NULL," +
          "	[HyperVHealthState] [int] NOT NULL," +
          "	[HyperVHeartbeat] [int] NOT NULL," +
          "	[HpcState] [int] NOT NULL," +
          "	[LastStateCheck] [datetime] NOT NULL," +
          "	[IsCheckpointed] [bit] NOT NULL," +
          "	[Ipv4Address] [nvarchar](15) COLLATE Latin1_General_CI_AS NOT NULL," +
          "	[Ipv6Address] [nvarchar](39) COLLATE Latin1_General_CI_AS NULL," +
          " CONSTRAINT [PK_VirtualMachinesActive] PRIMARY KEY CLUSTERED" +
          " ( [ID] ASC ) WITH ( PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF ) ON [PRIMARY]" +
          " ) ON [PRIMARY]"

        exNonQ conn sqlCmdText |> ignore

        // Create the VirtualMachinesPendingCreation table
        let sqlCmdText =
          "CREATE TABLE [dbo].[VirtualMachinesPendingCreation](" +
          "	[ID] [int] IDENTITY(1,1) NOT NULL," +
          "	[VmDataID] [int] NOT NULL," +
          "	[HostServer] [nvarchar](64) COLLATE Latin1_General_CI_AS NOT NULL," +
          "	[Password] [nvarchar](128) COLLATE Latin1_General_CI_AS NOT NULL," +
          " CONSTRAINT [PK_VirtualMachinesPendingCreation] PRIMARY KEY CLUSTERED" +
          " ( [ID] ASC ) WITH ( PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF ) ON [PRIMARY]" +
          " ) ON [PRIMARY]"

        exNonQ conn sqlCmdText |> ignore

        // Create the HyperVNodes table
        let sqlCmdText =
          "CREATE TABLE [dbo].[HyperVNodes](" +
          " [ID] [int] IDENTITY(1,1) NOT NULL," +
          " [HostName] [nvarchar](65) COLLATE Latin1_General_CI_AS NOT NULL UNIQUE," +
          " [IpAddress] [nvarchar](15) COLLATE Latin1_General_CI_AS NULL," +
          " [ExternalNIC] [uniqueidentifier] NOT NULL," +
          " [CpuCount] [int] NOT NULL," +
          " [MemorySize] [bigint] NOT NULL," +
          " [IsReachable] [bit] NOT NULL," +
          " [LastCpuLoad] [real] NULL," +
          " [LastMemoryFree] [bigint] NULL," +
          " [LastCounterUpdated] [datetime] NOT NULL," +
          " CONSTRAINT [PK_HyperVNodes] PRIMARY KEY CLUSTERED" +
          " ( [ID] ASC ) WITH ( PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF ) ON [PRIMARY]" +
          " ) ON [PRIMARY]"

        exNonQ conn sqlCmdText |> ignore

        // Create the VMTemplates table
        let sqlCmdText =
          "CREATE TABLE [dbo].[VMTemplates](" +
          " [ID] [int] IDENTITY(1,1) NOT NULL," +
          " [IsDeleted] [bit] NOT NULL," +
          " [Name] [nvarchar](65) COLLATE Latin1_General_CI_AS NOT NULL UNIQUE," +
          "	[Description] [ntext] COLLATE Latin1_General_CI_AS NULL," +
          "	[CpuCountMin] [int] NOT NULL," +
          "	[CpuCountMax] [int] NOT NULL," +
          "	[MemorySizeMbMin] [int] NOT NULL," +
          "	[MemorySizeMbMax] [int] NOT NULL," +
          " [NicSwitchName] [nvarchar](65) COLLATE Latin1_General_CI_AS NOT NULL," +
          "	[VhdPath] [nvarchar](256) COLLATE Latin1_General_CI_AS NOT NULL," +
          " [ParentTemplateID] [int] NOT NULL," +
          " [IsHpcNode] [bit] NOT NULL," +
          "	[HpcScheduler] [nvarchar](64) COLLATE Latin1_General_CI_AS NULL," +
          " CONSTRAINT [PK_VMTemplates] PRIMARY KEY CLUSTERED" +
          " ( [ID] ASC ) WITH ( PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF ) ON [PRIMARY]" +
          " ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"

        exNonQ conn sqlCmdText |> ignore

        // Create the MessagesToUsers table
        let sqlCmdText =
          "CREATE TABLE [dbo].[MessagesToUsers](" +
          " [ID] [int] IDENTITY(1,1) NOT NULL," +
          " [Recipient] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL," +
          " [DateTime] [datetime] NOT NULL," +
          " [Message] [ntext] COLLATE Latin1_General_CI_AS NOT NULL," +
          " CONSTRAINT [PK_MessagesToUsers] PRIMARY KEY CLUSTERED" +
          " ( [ID] ASC ) WITH ( PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF ) ON [PRIMARY]" +
          " ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"

        exNonQ conn sqlCmdText |> ignore

        // Create the GeneralConfig table
        let sqlCmdText =
          "CREATE TABLE [dbo].[GeneralConfig](" +
          " [ID] [int] IDENTITY(1,1) NOT NULL," +
          " [ConfKey] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL," +
          " [ConfValue] [ntext] COLLATE Latin1_General_CI_AS NOT NULL," +
          " CONSTRAINT [PK_GeneralConfig] PRIMARY KEY CLUSTERED" +
          " ( [ID] ASC ) WITH ( PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF ) ON [PRIMARY]" +
          " ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"

        exNonQ conn sqlCmdText |> ignore

    finally
        conn.Close()


let deleteDatabaseFile () =

    let exNonQ conn query =
      use cmd = new SqlCommand(query, conn, CommandTimeout=10)
      cmd.ExecuteNonQuery()

    use conn = new System.Data.SqlClient.SqlConnection()
    conn.ConnectionString <- @"Data Source=.\SQLExpress;Initial Catalog=master;Integrated Security=SSPI"
    conn.Open()

    try
        exNonQ conn "USE [master]" |> ignore

        let sqlCmdText = "IF EXISTS (SELECT name FROM sys.databases WHERE name = N'OctopusDb') " +
                         "DROP DATABASE [OctopusDb]"
        exNonQ conn sqlCmdText |> ignore

    finally
        conn.Close()
